package base.db.lib.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
import org.apache.commons.lang.StringUtils;
import base.db.lib.DBDAO;

/**
 * 批量操作
 * 
 */
@SuppressWarnings("all")
public class DAOExecuteUtil {
	private static String name;

	public static boolean executeSQLUtil(String sql, Vector params)
			throws Exception {
		boolean result = false;
		Connection connection = ConncenFactory.getConnection();// 设置数据源
		PreparedStatement ps = null;
		ps = connection.prepareStatement(sql);
		setSQLValues(ps, (Vector) params); // 设置参数
		int row = -1;
		row = ps.executeUpdate();
		connection.commit();
		System.out.println("LOG: [ base.db.lib.util.DAOExecuteUtil]: " + sql);
		System.out.println("LOG: [base.db.lib.util.DAOExecuteUtil] It is OK to do SQL execute!["
						+ row + "]");
		if (row >= 0) {
			result = true;
		}

		return result;

	}

	/**
	 * 执行SQL语句 操作成功返回 true 否返回 false
	 * 
	 * @param SQL
	 *            语句
	 * @param params
	 *            参数 说明 修改或添加
	 */
	public static boolean executeSQL(String sql, Vector params) throws Exception {
		if (StringUtils.isEmpty(sql))
			return false;
		int result = 0;
		DBDAO dao = ConncenFactory.getDao();
	
			dao.setConnection(ConncenFactory.getConnection()); // 设置数据源
			result = dao.execute(sql, params);
		
		if (result > 0) {
			return true;
		}
		return false;
	}

	/**
	 * 执行SQL语句 操作成功返回 true 否返回 false
	 * 
	 * @param SQL
	 *            语句
	 */
	public static boolean executeSQL(String sql) throws Exception {
		if (StringUtils.isEmpty(sql))
			return false;
		int result = 0;
		DBDAO dao = ConncenFactory.getDao();

			dao.setConnection(ConncenFactory.getConnection()); // 设置数据源
			result = dao.execute(sql, null);
		
		if (result > 0) {
			return true;
		}
		return false;
	}

	/**
	 * 批量增加 操作成功返回 true 否返回 false
	 * 
	 * @param tablename
	 *            表名
	 * @param cols
	 *            列名集合(List--string)
	 *@param params
	 *            参数集合 (List ----Vector)
	 */
	public static boolean addList(String tablename, List cols, List params)throws Exception {
		if (StringUtils.isEmpty(tablename) || cols == null || cols.size() == 0
				|| params == null || params.size() == 0) {
			return false;
		}
		Connection connection = ConncenFactory.getConnection();// 设置数据源
		PreparedStatement ps = null;
		int[] result = null;
		try {
			connection.setAutoCommit(false);
			String inserSql = setInsertSQL(tablename, cols);
			System.out.println("LOG: 批量增加！");
			System.out.println("LOG: [base.db.lib.util.DAOExecuteUtil] "
					+ inserSql);
			ps = connection.prepareStatement(inserSql);
			for (Object param : params) {
				if (param instanceof Vector) {
					setSQLValues(ps, (Vector) param);
					ps.addBatch();
				}
			}
			result = ps.executeBatch();
			connection.commit();
		} catch (Exception e) {
			try {
				connection.rollback();// 事务回滚
			} catch (Exception e2) {
				e2.printStackTrace();
			}
			System.out.println("LOG:  数据库添加操作异常 ============== " + tablename);
			e.printStackTrace();
			return false;
		} finally {
			// ConncenFactory.colseConnection();// 关闭连接 最后处理
		}
		if (result != null && result.length > 0) {
			return true;
		}
		return false;
	}

	/**
	 * 批量增加 操作成功返回 true 否返回 false
	 * 
	 * @param tablename
	 *            表名
	 * @param cols
	 *            列名集合(List--string)
	 *@param params
	 *            参数集合 (List ----Vector)
	 */
	public static boolean addListParam(String tablename, List cols, List params) {
		if (StringUtils.isEmpty(tablename) || cols == null || cols.size() == 0
				|| params == null || params.size() == 0) {
			return false;
		}
		Connection connection = ConncenFactory.getConnection();// 设置数据源
		Statement ps = null;
		int[] result = null;
		try {
			connection.setAutoCommit(false);
			System.out.println("LOG:  批量增加！");
			ps = connection.createStatement();
			for (Object param : params) {
				if (param instanceof Vector) {
					String inserSql = setInsertSQLParam(tablename, cols,
							(Vector) param);
					ps.addBatch(inserSql);
				}
			}
			result = ps.executeBatch();
			connection.commit();
		} catch (Exception e) {
			try {
				connection.rollback();// 事务回滚
			} catch (Exception e2) {
				e2.printStackTrace();
			}
			System.out.println("LOG:   数据库添加操作异常 ============== " + tablename);
			e.printStackTrace();
			return false;
		} finally {
			// ConncenFactory.colseConnection();// 关闭连接 最后处理
		}
		if (result != null && result.length > 0) {
			return true;
		}
		return false;
	}

	/**
	 * 批量增加 操作成功返回 true 否返回 false
	 * 
	 * @param tablename
	 *            表名
	 * @param cols
	 *            列名集合(List--String)
	 *@param params
	 *            参数 集合(List ----Vector)
	 *@param condtions
	 *            条件参数集合 (List ----String)
	 *@param condtion
	 *            条件SQL( where code=?)
	 */
	public static boolean updateList(String tablename, List cols, List params,
			List condtions, String condtion) {
		if (StringUtils.isEmpty(tablename) || cols == null || cols.size() == 0
				|| params == null || params.size() == 0 || condtions == null
				|| condtions.size() == 0 || StringUtils.isEmpty(condtion)) {
			return false;
		}
		Connection connection = ConncenFactory.getConnection();// 设置数据源

		PreparedStatement ps = null;
		int[] result = null;

		try {
			connection.setAutoCommit(false);
			String updateSql = setUpdateSQL(tablename, cols, condtion);
			ps = connection.prepareStatement(updateSql);
			System.out.println("LOG:  批量修改！");
			System.out.println("LOG: [base.db.lib.util.DAOExecuteUtil] "
					+ updateSql);
			for (int i = 0; i < params.size(); i++) {
				Object param = params.get(i);
				if (param instanceof Vector) {
					setSQLValues(ps, (Vector) param);
					ps.setObject(((Vector) param).size() + 1, condtions.get(i));
					ps.addBatch();
				}
			}
			result = ps.executeBatch();
			connection.commit();
		} catch (Exception e) {
			try {
				connection.rollback();// 事务回滚
			} catch (SQLException e1) {

				e1.printStackTrace();
			}
			System.out.println("LOG:  数据库添加操作异常 ============== " + tablename);
			e.printStackTrace();
			return false;
		} finally {
			// ConncenFactory.colseConnection();// 关闭连接 最后处理
		}

		if (result != null && result.length > 0) {
			return true;
		}
		return false;

	}

	/**
	 * 设置修改格式
	 * 
	 * @param tablename
	 *            表名
	 * @param cols
	 *            列名
	 * @return
	 * @throws Exception
	 */
	public static String setInsertSQL(String tablename, List cols)
			throws Exception {
		StringBuilder insertSql = new StringBuilder("");
		insertSql.append("insert into ");
		insertSql.append(tablename);
		insertSql.append(" (");
		for (int i = 0; i < cols.size(); ++i) {
			insertSql.append((String) cols.get(i));
			if (i == cols.size() - 1) {
				insertSql.append(")");
			} else {
				insertSql.append(",");
			}
		}
		insertSql.append(" values(");
		for (int i = 0; i < cols.size(); ++i) {
			if (i == cols.size() - 1) {
				insertSql.append("?)");
			} else {
				insertSql.append("?,");
			}
		}
		return insertSql.toString();
	}

	/**
	 * 设置修改sql参数
	 * 
	 * @param tablename
	 *            表名
	 * @param cols
	 *            列名
	 * @param condtions
	 *            修改条件
	 * @return
	 * @throws Exception
	 */
	public static String setUpdateSQL(String tablename, List cols,
			String condtion) throws Exception {
		StringBuilder updateSql = new StringBuilder("");
		updateSql.append("update ");
		updateSql.append(tablename);
		updateSql.append(" set  ");
		for (int i = 0; i < cols.size(); ++i) {
			updateSql.append((String) cols.get(i));
			updateSql.append("=?");
			if (i == cols.size() - 1)
				continue;
			updateSql.append(", ");
		}
		updateSql.append(condtion);
		return updateSql.toString();
	}

	/**
	 * 将得到的参数设置到PreparedStatement中
	 * 
	 * @param pst
	 *            PreparedStatement
	 * @param vlist
	 *            Vector 参数值
	 * @throws Exception
	 */
	public static void setSQLValues(PreparedStatement pst, Vector vlist)
			throws Exception {
		try {
			if (vlist == null)
				return;
			Object v = null;
			for (int i = 0; i < vlist.size(); ++i) {
				v = vlist.get(i);
				if (v instanceof java.util.Date) {
					java.util.Date d = (java.util.Date) v;
					v = new Timestamp(d.getTime());
				}
				pst.setObject(i + 1, v);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			throw new Exception(ex);
		}
	}

	/**
	 * 采用 标准插入SQL 语言
	 * 
	 * @param tablename
	 *            表名
	 * @param cols
	 *            列名
	 * @param params
	 *            参数值
	 * @return
	 * @throws Exception
	 */
	public static String setInsertSQLParam(String tablename, List cols,
			Vector params) throws Exception {
		StringBuilder insertSql = new StringBuilder("");
		insertSql.append("insert into ");
		insertSql.append(tablename);
		insertSql.append(" (");
		for (int i = 0; i < cols.size(); ++i) {
			insertSql.append((String) cols.get(i));
			if (i == cols.size() - 1) {
				insertSql.append(")");
			} else {
				insertSql.append(",");
			}
		}
		insertSql.append(" values(");
		for (int i = 0; i < cols.size(); ++i) {
			if (i == cols.size() - 1) {
				insertSql.append(params.get(i).toString() + ")");
			} else {
				insertSql.append(params.get(i).toString() + ",");
			}
		}
		return insertSql.toString();
	}
}
